#!/usr/bin/env python
# -*- coding: utf-8 -*-
# SQL type wrappers audited by Joshua Kugler, 2008-02-20
"""
The tmpo_report table holds data used for staff to generate and display 
customized SQL queries as reports. This API manages all access to or 
modification of that table.

    +----------------------+-----------------+--------------------------+
    |     Column Name      |       Type      |       Description        |
    +----------------------+-----------------+--------------------------+
    | id                   | int,pk,identity | Unique record identifier |
    | title                | varchar         | Report title             |
    | description          | varchar         | Report description       |
    | query_values         | varchar         | For future use           |
    | query                | varchar         | Database query as SQL    |
    | active               | bit             | logical delete flag      |
    +----------------------+-----------------+--------------------------+

The functions found in this API are:

 get_results
     This function retrieves a single row from the database, reads the
     SQL query, and executes it, building a result set for the report
     display. Uses id as the filter.
     
 replace_with_dict_values
    cycles through the form fields, and parses them appropriately using
    the sql string/int functions.
 
 make_column_list
    creates the list of column headers for the report display, using
    the cursor description values.
    
 make_data_list
    creates the rows of data, matching them up with the appropriate
    column headers
"""
from string import Template
from datetime import datetime
from tack.db import query, sql_int, sql_unstring, sql_string
from tack.lib import string_app, content_headers, status_codes, json_app
##from tack.lib.jsontools import json_dumps

## TODO:  replace json_dumps + string_app with json_app

sql_get_one = """
SELECT
    * 
FROM
    tmpo_report
WHERE
    id = $report_id
    AND active = 1
"""

def get_results(environ, start_response):
    try:
        report_id = environ['dispatcher.groups']['report_id']
        sql = Template(sql_get_one).substitute(locals())
        cursor = environ['connection'].cursor()
        cursor.execute(sql)
        rows = cursor.fetchall()
        if rows:
            output = {}
            form = environ['form']
            sql = rows[0].query
            sql = sql_unstring(sql)
            sql = replace_with_dict_values(sql, form)
            cursor = environ['connection'].cursor()
            cursor.execute(sql)
            rows = cursor.fetchall()
            output['columns'] = make_column_list(cursor.description)
            row_data = []
            for row in rows:
                row_data.append(make_data_list(cursor.description, row))
            output['data'] = row_data
        else:
            start_response(status_codes.not_found, content_headers.default_plain())
            return ['404 Not Found']
    except:
        output = {}
        output['columns'] = ['Report Error']
        output['data'] = [['This report would not run: ' + sql]]
    ##output = json_dumps(output, indent=2)
    return json_app(output)(environ, start_response)

def replace_with_dict_values(sql, form):
    for key, value in form.iteritems():
        if key == '$Vehicle' or key == '$Driver':
            value = str(sql_int(value))
        else:
            value = sql_string(value)
        sql = sql.replace(key, value)
    return sql

def make_column_list(cursor_description):
    column_list = []
    for column in cursor_description:
        column_list.append(column[0])
    return column_list

def make_data_list(cursor_description, row):
    value_list = []
    for i, c in enumerate(row):
        if cursor_description[i][1] == datetime:
            row[i] = row[i].strftime('%Y-%m-%d %H:%M') if row[i] else None
        value_list.append(row[i])
    return value_list
